package database;
import java.sql.ResultSet;
	import java.sql.SQLException;
	import java.sql.Statement;
	import java.util.ArrayList;

import databaseInterface.BookManageInterface;

import po.BookPO;
import po.CustomerPO;
public class BookManage implements BookManageInterface{
	
		/**
		 * @param args
		 */
		
		Statement stmt;
		DatabaseConnection db= new DatabaseConnection();
		
		public void connect (){
	        
	         stmt =db.connect();
	    }
	    
		public  void insert (BookPO b){
	    	 String insert ="insert into book values ('"+b.getISBN()+"','"+b.getAuthor()+"','"+b.getTitle()+"','"+b.getPress()+"','"+b.getPubishingTime()+"','"+b.getCat()+"',"+b.getPrice()+","+b.getNum()+")";
	         try {
				stmt.executeUpdate(insert);
			} catch (SQLException e) {
				// TODO Auto-generated catch block
				e.printStackTrace();
			}
	    
	   }

		public	void delete (String isbn ){
	    	
	    	String delete="delete from book where ISBN= '"+isbn+"'" ;
	    	try {
				stmt.executeUpdate(delete);
			} catch (SQLException e) {
				// TODO Auto-generated catch block
				e.printStackTrace();
			}
	    }
	    
		public  void update(BookPO b,String isbn){
	    	
	    	String update ="update book set ISBN ='"+b.getISBN()+"', author = '"+b.getAuthor()+"', title = '"+b.getTitle()+"', press = '"+b.getPress()+"', publishing time = '"+b.getPubishingTime()+"',catalog = '"+b.getCat()+"', price  = "+b.getPrice()+", num = "+b.getNum()+" where ISBN = '"+isbn+"'" ;
	        try {
				stmt.executeUpdate(update);
			} catch (SQLException e) {
				// TODO Auto-generated catch block
				e.printStackTrace();
			}    
	    }
		
		public  ArrayList<BookPO> query ( ){
	    	String query = "select * from book ";
	        ArrayList<BookPO>  b= query(query);
	        return b;
	  }
	 
		public  ArrayList<BookPO> queryByISBN (String isbn ){
	    	String query = "select * from book where isbn = '"+isbn+"'";
	        ArrayList<BookPO>  b= query(query);
	        return b;
	  }
		public  ArrayList<BookPO> queryByTitle  (String t ){
	    	String query = "select * from book where title like '%"+t+"%'";
	        ArrayList<BookPO>  b= query(query);
	        return b;
	  }
		
		public ArrayList<BookPO> queryByPress (String p ){
	    	String query = "select * from book where press = '"+p+"'";
	        ArrayList<BookPO>  b= query(query);
	        return b;
	  }
		public  ArrayList<BookPO> queryByAuthor (String a ){
	    	String query = "select * from book where author = '"+a+"'";
	        ArrayList<BookPO>  b= query(query);
	        return b;
	  }
		public ArrayList<BookPO> queryByCatalog (String cat ){
	    	String query = "select * from book where catalog = '"+cat+"'";
	        ArrayList<BookPO>  b= query(query);
	        return b;
	  }
	  
	  
	  private ArrayList<BookPO> query (String query ){
	    	
	    	ArrayList<BookPO> booklist = new ArrayList<BookPO> ();
	      	try {
				ResultSet rs= stmt.executeQuery(query);
			
	    	
	    	while(rs.next()){
	    		BookPO b= toBookPO(rs);
	    	    booklist.add(b);
	    	
	    	}
	    	} catch (SQLException e) {
				// TODO Auto-generated catch block
				e.printStackTrace();
			}
	    	
	    	return booklist;	
	    }
	    private BookPO toBookPO(ResultSet rs) {
		// TODO Auto-generated method stub
	    	BookPO b = new BookPO();
	    	try {
	    		b.setIsbn(rs.getString(1));
	    		b.setAuthor( rs.getString(2));
	    		b.setTitle(rs.getString(3));
	    		b.setPress(rs.getString(4));
	    		b.setPublishedTime( rs.getString(5));
	    		b.setCat(rs.getString(6));
	    		b.setPrice( rs.getDouble(7));
	    		b.setNum(rs.getInt(8));
			} catch (SQLException e) {
				// TODO Auto-generated catch block
				e.printStackTrace();
			}
		return b;
	}
	    
	    public 	void close(){
	      db.close();
	    }
	   

	    public static void main(String[]args){
	    	BookManage e = new BookManage();
	    	e.connect();
	    	ArrayList<BookPO> a =e.queryByTitle("g");
	    	
	    	for(int i=0;i<a.size();i++)
	    	    System.out.println(a.get(i).getTitle());
	    	e.close();
	    }
	

}
